package com.test.springCloud.service.impl;

import cn.hutool.crypto.digest.DigestUtil;
import com.alibaba.fastjson.JSONObject;
import com.test.springCloud.mapper.UserMapper;
import com.test.springCloud.service.ExcelService;
import com.test.springCloud.service.UploadImgService;
import com.test.springCloud.utils.*;
import com.test.springcloud.pojo.RolePojo;
import com.test.springcloud.pojo.UserPojo;
import io.swagger.models.auth.In;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

@Service("ExcelService")
@Slf4j
public class ExcelServiceImpl implements ExcelService {

    @Resource
    UserMapper userMapper;

    @Resource
    UploadImgService imgService;
    /**
     * 导入excel表
     * @param file
     * @return
     */
    public ResultUtils uploadExcel(MultipartFile file,HttpServletRequest request) {
        ResultUtils resultUtils = null;
        //解析token
        String token = request.getHeader("token");
        String userId = JwtUtil.getUserId(token);
        //获取当前登录的用户信息
        Integer uid = Integer.parseInt(userId);
        UserPojo user = userMapper.getUserById(uid);
        Integer roleId = user.getRoleId();
        RolePojo role = userMapper.getRoleById(roleId);
        try {
            if (file.isEmpty()){
                resultUtils = ResultUtils.getFail("请选择要上传的文件");
                resultUtils.setCode(4027);

                return resultUtils;
            }
            //获取源文件名
            String originalFilename = file.getOriginalFilename();
            String suffix = originalFilename.substring(originalFilename.lastIndexOf("."));
            if (!FileExceptImgUtil.FILE_EXTENSIONS.contains(suffix)){
                resultUtils = ResultUtils.getFail("该文件不符合要求，请重新上传文件");
                resultUtils.setCode(4028);

                return resultUtils;
            }
            //老师只能批量导入学生，管理员则可以导入学生和老师
            if ("管理员".equals(role.getRoleName())){
                List<UserPojo> list = new ArrayList<>();
                //获取到工作簿
                Workbook workbook = WorkbookFactory.create(file.getInputStream());
                Sheet sheet1 = workbook.getSheet("Sheet1");
                int rowNum = sheet1.getLastRowNum();//总行数
                short cellNum = sheet1.getRow(0).getLastCellNum();//总列数
                //遍历每一行
                for (int i = 0; i <=rowNum ; i++) {
                    Row row = sheet1.getRow(i);
                    UserPojo userPojo = new UserPojo();

                    Cell cell1 = row.getCell(0);
                    cell1.setCellType(CellType.STRING);//数字或其他字符需要转换为String类型
                    userPojo.setUserName(cell1.getStringCellValue());

                    Cell cell2 = row.getCell(1);
                    cell2.setCellType(CellType.STRING);
                    String md5Hex = DigestUtil.md5Hex(cell2.getStringCellValue());
                    userPojo.setUserPwd(md5Hex);

                    Cell cell3 = row.getCell(2);
                    cell3.setCellType(CellType.STRING);
                    userPojo.setNickName(cell3.getStringCellValue());

                    Cell cell4 = row.getCell(4);
                    cell4.setCellType(CellType.STRING);
                    if ("老师".equals(cell4.getStringCellValue())){
                        userPojo.setRoleId(2);
                    }else if ("学生".equals(cell4.getStringCellValue())){
                        userPojo.setRoleId(3);
                    }

                    Cell cell5 = row.getCell(5);
                    cell5.setCellType(CellType.STRING);
                    Integer age = Integer.parseInt(cell5.getStringCellValue());
                    userPojo.setAge(age);

                    Cell cell6 = row.getCell(6);
                    cell6.setCellType(CellType.STRING);
                    userPojo.setSex(cell6.getStringCellValue());

                    Cell cell7 = row.getCell(7);
                    cell7.setCellType(CellType.STRING);
                    userPojo.setAddress(cell7.getStringCellValue());

                    Cell cell8 = row.getCell(8);
                    cell8.setCellType(CellType.STRING);
                    if ("".equals(cell8.getStringCellValue())||null==cell8.getStringCellValue()){
                        String img = DefaultImg.getDefaultBase64Img();
                        //base64转为multipartFile文件；
                        Base64ToImage bImage = new Base64ToImage();
                        MultipartFile f = bImage.base64MultiFile(img);
                        // 类型转换
                        MultipartFile[] files = new MultipartFile[1];
                        files[0] = f;
                        // 进行图片上传
                        JSONObject uploadServerImgResultObject = imgService.uploadAndStoreDefaultImgToWebIndex(files);
                        // 获得上传后的路径
                        Integer successCount = uploadServerImgResultObject.getInteger("successCount");
                        String imgUrlArrayString = "";
                        for (int j = 0; j < successCount; j++) {
                            if (i != successCount - 1)
                                imgUrlArrayString += uploadServerImgResultObject.getJSONObject("data").getString("remotePicUri" + j)+ ";";
                            else
                                imgUrlArrayString += uploadServerImgResultObject.getJSONObject("data").getString("remotePicUri" + j);
                        }
                        userPojo.setAvatar(imgUrlArrayString);
                    }else {
                        //base64转为multipartFile文件；
                        Base64ToImage bImage = new Base64ToImage();
                        MultipartFile f = bImage.base64MultiFile(cell8.getStringCellValue());
                        // 类型转换
                        MultipartFile[] files = new MultipartFile[1];
                        files[0] = f;
                        // 进行图片上传
                        JSONObject uploadServerImgResultObject = imgService.uploadAndStoreDefaultImgToWebIndex(files);
                        // 获得上传后的路径
                        Integer successCount = uploadServerImgResultObject.getInteger("successCount");
                        String imgUrlArrayString = "";
                        for (int j = 0; j < successCount; j++) {
                            if (i != successCount - 1)
                                imgUrlArrayString += uploadServerImgResultObject.getJSONObject("data").getString("remotePicUri" + j)+ ";";
                            else
                                imgUrlArrayString += uploadServerImgResultObject.getJSONObject("data").getString("remotePicUri" + j);
                        }
                        userPojo.setAvatar(imgUrlArrayString);
                    }
                    list.add(userPojo);
                }
                Integer size = userMapper.addListUser(list);
                if (size>0){
                    resultUtils = ResultUtils.getSuccess("数据添加成功！");
                    resultUtils.setCode(200);
                    return resultUtils;
                }
                resultUtils = ResultUtils.getFail("数据添加失败！");
                resultUtils.setCode(4029);
                return resultUtils;
            }else if ("老师".equals(role.getRoleName())){
                List<UserPojo> list = new ArrayList<>();
                //获取到工作簿
                Workbook workbook = WorkbookFactory.create(file.getInputStream());
                Sheet sheet1 = workbook.getSheet("Sheet1");
                int rowNum = sheet1.getLastRowNum();//总行数
                short cellNum = sheet1.getRow(0).getLastCellNum();//总列数
                //遍历每一行
                for (int i = 0; i <=rowNum ; i++) {
                    Row row = sheet1.getRow(i);
                    UserPojo userPojo = new UserPojo();

                    Cell cell1 = row.getCell(0);
                    cell1.setCellType(CellType.STRING);//数字或其他字符需要转换为String类型
                    userPojo.setUserName(cell1.getStringCellValue());

                    Cell cell2 = row.getCell(1);
                    cell2.setCellType(CellType.STRING);
                    String md5Hex = DigestUtil.md5Hex(cell2.getStringCellValue());
                    userPojo.setUserPwd(md5Hex);

                    Cell cell3 = row.getCell(2);
                    cell3.setCellType(CellType.STRING);
                    userPojo.setNickName(cell3.getStringCellValue());

                    Cell cell4 = row.getCell(4);
                    cell4.setCellType(CellType.STRING);
                    if ("老师".equals(cell4.getStringCellValue())){
                        userPojo.setRoleId(2);
                    }else if ("学生".equals(cell4.getStringCellValue())){
                        userPojo.setRoleId(3);
                    }

                    Cell cell5 = row.getCell(5);
                    cell5.setCellType(CellType.STRING);
                    Integer age = Integer.parseInt(cell5.getStringCellValue());
                    userPojo.setAge(age);

                    Cell cell6 = row.getCell(6);
                    cell6.setCellType(CellType.STRING);
                    userPojo.setSex(cell6.getStringCellValue());

                    Cell cell7 = row.getCell(7);
                    cell7.setCellType(CellType.STRING);
                    userPojo.setAddress(cell7.getStringCellValue());

                    Cell cell8 = row.getCell(8);
                    cell8.setCellType(CellType.STRING);
                    if ("".equals(cell8.getStringCellValue())||null==cell8.getStringCellValue()){
                        String img = DefaultImg.getDefaultBase64Img();
                        //base64转为multipartFile文件；
                        Base64ToImage bImage = new Base64ToImage();
                        MultipartFile f = bImage.base64MultiFile(img);
                        // 类型转换
                        MultipartFile[] files = new MultipartFile[1];
                        files[0] = f;
                        // 进行图片上传
                        JSONObject uploadServerImgResultObject = imgService.uploadAndStoreDefaultImgToWebIndex(files);
                        // 获得上传后的路径
                        Integer successCount = uploadServerImgResultObject.getInteger("successCount");
                        String imgUrlArrayString = "";
                        for (int j = 0; j < successCount; j++) {
                            if (i != successCount - 1)
                                imgUrlArrayString += uploadServerImgResultObject.getJSONObject("data").getString("remotePicUri" + j)+ ";";
                            else
                                imgUrlArrayString += uploadServerImgResultObject.getJSONObject("data").getString("remotePicUri" + j);
                        }
                        userPojo.setAvatar(imgUrlArrayString);
                    }else {
                        //base64转为multipartFile文件；
                        Base64ToImage bImage = new Base64ToImage();
                        MultipartFile f = bImage.base64MultiFile(cell8.getStringCellValue());
                        // 类型转换
                        MultipartFile[] files = new MultipartFile[1];
                        files[0] = f;
                        // 进行图片上传
                        JSONObject uploadServerImgResultObject = imgService.uploadAndStoreDefaultImgToWebIndex(files);
                        // 获得上传后的路径
                        Integer successCount = uploadServerImgResultObject.getInteger("successCount");
                        String imgUrlArrayString = "";
                        for (int j = 0; j < successCount; j++) {
                            if (i != successCount - 1)
                                imgUrlArrayString += uploadServerImgResultObject.getJSONObject("data").getString("remotePicUri" + j)+ ";";
                            else
                                imgUrlArrayString += uploadServerImgResultObject.getJSONObject("data").getString("remotePicUri" + j);
                        }
                        userPojo.setAvatar(imgUrlArrayString);
                    }
                    if ("老师".equals(cell4.getStringCellValue())||"老师"==cell4.getStringCellValue())
                        continue;
                    else
                        list.add(userPojo);
                }
                Integer size = userMapper.addListUser(list);
                if (size>0){
                    resultUtils = ResultUtils.getSuccess("数据添加成功！");
                    resultUtils.setCode(200);
                    return resultUtils;
                }
                resultUtils = ResultUtils.getFail("数据添加失败！");
                resultUtils.setCode(4029);
                return resultUtils;
            }
        }catch (Exception e){
            resultUtils = ResultUtils.getFail(e.getMessage());
            resultUtils.setCode(500);
            return  resultUtils;
        }
        resultUtils = ResultUtils.getFail("您无权限导入数据！");
        resultUtils.setCode(4030);
        return  resultUtils;
    }

    /**
     * 导出excel表格
     * @return request
     */
    public ResultUtils downloadExcel(HttpServletRequest request) {

        ResultUtils resultUtils = null;
        //解析token
        String token = request.getHeader("token");
        String userId = JwtUtil.getUserId(token);
        //获取当前登录的用户信息
        Integer uid = Integer.parseInt(userId);
        UserPojo user = userMapper.getUserById(uid);
        Integer roleId = user.getRoleId();
        RolePojo role = userMapper.getRoleById(roleId);
        //角色为教师才能有导出的权限
        if ("老师".equals(role.getRoleName())){
            //根据id查询数据
            List<UserPojo> userList = userMapper.getAllUser();
            if (userList == null || userList.isEmpty()) {
                resultUtils = ResultUtils.getFail("failed");
                resultUtils.setCode(4024);
                return resultUtils;
            }
            //生成文件路径
            String newFilePath = "D:\\ExcelExport";
            //文件名称
            String fileName = "user.xlsx";
            //输出流
            OutputStream out = null;
            //Excel文件
            XSSFWorkbook workBook = new XSSFWorkbook();
            //Excel页脚
            XSSFSheet sheet = workBook.createSheet("数据导出");
            //设置列的宽度
            sheet.setDefaultColumnWidth(16);
            //创建标题行
            XSSFRow titleRow = sheet.createRow(0);
            String[] title = new String[]{"id", "userName", "nickName", "address","age","sex","account","avatar","roleId","pwd"};
            //设置标题字体样式
            XSSFCellStyle cellStyle = workBook.createCellStyle();
            XSSFFont font = workBook.createFont();
            font.setBold(true);//加粗
            font.setFontHeightInPoints((short) 14);//设置字体大小
            cellStyle.setFont(font);
            //设置标题列
            for (int i = 0; i < title.length; i++) {
                //创建标题的单元格
                XSSFCell titleCell = titleRow.createCell(i);
                //填充标题数值
                titleCell.setCellValue(title[i]);
                //设置样式
                titleCell.setCellStyle(cellStyle);
            }
            //填充数据
            //第一行是标题所以要从第二行开始
            for (int i = 0; i < userList.size(); i++) {
                UserPojo users = userList.get(i);
                XSSFRow row = sheet.createRow(i + 1);
                for (int j = 0; j < title.length; j++) {
                    XSSFCell titleCell = row.createCell(j);
                    String exportKey = title[j];
                    switch (exportKey) {
                        case "id":
                            titleCell.setCellValue(users.getUserId());
                            break;
                        case "userName":
                            titleCell.setCellValue(users.getUserName());
                            break;
                        case "nickName":
                            titleCell.setCellValue(users.getNickName());
                            break;
                        case "address":
                            titleCell.setCellValue(users.getAddress());
                            break;
                        case "age":
                            titleCell.setCellValue(users.getAge());
                            break;
                        case "sex":
                            titleCell.setCellValue(users.getSex());
                            break;
                        case "account":
                            titleCell.setCellValue(users.getAccount());
                            break;
                        case "avatar":
                            titleCell.setCellValue(users.getAvatar());
                            break;
                        case "roleId":
                            titleCell.setCellValue(users.getRoleId());
                            break;
                        case "pwd":
                            titleCell.setCellValue(users.getUserPwd());
                            break;
                    }
                }
            }
            try {
                File file = new File(newFilePath + File.separator + fileName);
                out = new FileOutputStream(file);
                workBook.write(out);
                out.flush();
                out.close();
            } catch (Exception e) {
                log.info(e.getMessage());
            }
            resultUtils = ResultUtils.getSuccess("success");
            resultUtils.setCode(200);
            return resultUtils;
        }
        resultUtils = ResultUtils.getFail("您无权导出文件！");
        resultUtils.setCode(4025);
        return resultUtils;
    }
}
